#region Using

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Globalization;
using BlogEngine.Core;
using BlogEngine.Core.Provider;
using BlogEngine.Core.Provider.Interfaces;

#endregion

namespace BlogEngine.Core.Provider.MSSQL
{
	/// <summary>
	/// Microsoft SQL Server Implementation of BlogProvider
	/// </summary>
	public class CIPages : IPages
	{

		/// <summary>
		/// Retrieves a Page from the data store.
		/// </summary>
		public  Page Select(Guid id)
		{
			Page page = new Page();
			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("SELECT Title, Description, PageContent, DateCreated, " +
								  "DateModified, Keywords, IsPublished, IsFrontPage, Parent, ShowInList " +
								  "FROM be_Pages " +
								  "WHERE PageID = @id");

				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					rdr.Read();

					page.Id = id;

					if (!rdr.IsDBNull(0))
						page.Title = rdr.GetString(0);
					if (!rdr.IsDBNull(1))
						page.Description = rdr.GetString(1);
					if (!rdr.IsDBNull(2))
						page.Content = rdr.GetString(2);
					if (!rdr.IsDBNull(3))
						page.DateCreated = rdr.GetDateTime(3);
					if (!rdr.IsDBNull(4))
						page.DateModified = rdr.GetDateTime(4);
					if (!rdr.IsDBNull(5))
						page.Keywords = rdr.GetString(5);
					if (!rdr.IsDBNull(6))
						page.IsPublished = rdr.GetBoolean(6);
					if (!rdr.IsDBNull(7))
						page.IsFrontPage = rdr.GetBoolean(7);
					if (!rdr.IsDBNull(8))
						page.Parent = rdr.GetGuid(8);
					if (!rdr.IsDBNull(9))
						page.ShowInList = rdr.GetBoolean(9);

					rdr.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			return page;
		}

		/// <summary>
		/// Inserts a new Page to the data store.
		/// </summary>
		public  void Insert(Page page)
		{
			if (page == null)
				throw new ArgumentNullException("page");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("INSERT INTO be_Pages (PageID, Title, Description, PageContent, " +
							  "DateCreated, DateModified, Keywords, IsPublished, IsFrontPage, Parent, ShowInList) " +
							  "VALUES (@id, @title, @desc, @content, @created, @modified, @keywords, @ispublished, @isfrontpage, @parent, @showinlist)");

				myConn.AddInParameter("@id", DbType.Guid, page.Id);
				myConn.AddInParameter("@title", DbType.String, page.Title);
				myConn.AddInParameter("@desc", DbType.String, page.Description);
				myConn.AddInParameter("@content", DbType.String, page.Content);
				myConn.AddInParameter("@created", DbType.DateTime, page.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@modified", DbType.DateTime, page.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@keywords", DbType.String, page.Keywords);
				myConn.AddInParameter("@ispublished", DbType.Boolean, page.IsPublished);
				myConn.AddInParameter("@isfrontpage", DbType.Boolean, page.IsFrontPage);
				myConn.AddInParameter("@parent", DbType.Guid, page.Parent);
				myConn.AddInParameter("@showinlist", DbType.Boolean, page.ShowInList);
				myConn.ExecuteNonQuery();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Updates a Page in the data store.
		/// </summary>
		public  void Update(Page page)
		{
			if (page == null)
				throw new ArgumentNullException("page");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("UPDATE be_Pages " +
							  "SET Title = @title, Description = @desc, PageContent = @content, " +
							  "DateCreated = @created, DateModified = @modified, Keywords = @keywords, " +
							  "IsPublished = @ispublished, IsFrontPage = @isfrontpage, Parent = @parent, ShowInList = @showinlist " +
							  "WHERE PageID = @id");
				myConn.AddInParameter("@id", DbType.Guid, page.Id);
				myConn.AddInParameter("@title", DbType.String, page.Title);
				myConn.AddInParameter("@desc", DbType.String, page.Description);
				myConn.AddInParameter("@content", DbType.String, page.Content);
				myConn.AddInParameter("@created", DbType.DateTime, page.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@modified", DbType.DateTime, page.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@keywords", DbType.String, page.Keywords);
				myConn.AddInParameter("@ispublished", DbType.Boolean, page.IsPublished);
				myConn.AddInParameter("@isfrontpage", DbType.Boolean, page.IsFrontPage);
				myConn.AddInParameter("@parent", DbType.Guid, page.Parent);
				myConn.AddInParameter("@showinlist", DbType.Boolean, page.ShowInList);
				myConn.ExecuteNonQuery();

				if (!Page.Items.Contains(page))
					Page.Items.Add(page);

				Page.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Deletes a Page from the data store.
		/// </summary>
		public  void Delete(Page page)
		{
			if (page == null)
				throw new ArgumentNullException("page");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("DELETE FROM be_Pages WHERE PageID = @id");
				myConn.AddInParameter("@id", DbType.Guid, page.Id);
				myConn.ExecuteNonQuery();

				if (Page.Items.Contains(page))
					Page.Items.Remove(page);

				Page.Items.Sort();
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}
		}

		/// <summary>
		/// Retrieves all pages from the data store
		/// </summary>
		/// <returns>List of Pages</returns>
		public  List<Page> Fill()
		{
			List<Page> pages = new List<Page>();

			//for each entry found, cause a data load to occur - done this way so the reader is shut first!
			foreach (Guid guid in SQLUtil.GetGuids("SELECT PageID FROM be_Pages", Provider.DatabaseName))
				pages.Add(Page.Load(guid));

			pages.Sort();
			return pages;
		}
	}
}
